package com.njwb.oa.dao.impl;

import com.njwb.oa.dao.DeptDao;
import com.njwb.oa.entity.Dept;

import java.sql.*;

public class DeptDaoImpl implements DeptDao {
    //操作mysql数据库
    public void insert(Dept dept) {
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2.获取连接
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sju?serverTimezone=UTC","root","root");

            //3.获取SQL执行器
            String sql = "insert into dept(deptno,dname,dloc) values(?,?,?)";
            pstmt = conn.prepareStatement(sql);
            //4.执行sql
            //先给占位符赋值,实体映射到表
            pstmt.setInt(1,dept.getDeptNo());
            pstmt.setString(2,dept.getDname());
            pstmt.setString(3,dept.getDloc());

            //5.执行sql
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源
            if(null != pstmt){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public Dept findByNo(Integer deptNo) {
        Dept dept = null;
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2.获取连接
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sju?serverTimezone=UTC","root","root");

            //3.获取SQL执行器
            String sql = "select deptno,dname,dloc from dept where deptno = ?";
            pstmt = conn.prepareStatement(sql);
            //4.执行sql
            //先给占位符赋值,实体映射到表
            pstmt.setInt(1,deptNo);
            //5.执行sql
            rs = pstmt.executeQuery();
            if(rs.next()){//找到了
                dept = new Dept();
                //数据在数据库中
                //数据中的数据映射到实体对象中
                dept.setDeptNo(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setDloc(rs.getString("dloc"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源
            if(rs != pstmt){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != pstmt){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return dept;
    }
}
